import pymysql
import pandas as pd
import datetime

import pymysql.cursors
class Mysqlutils(object):
    def __init__(self):
        self.conn= pymysql.connect(
            host="localhost",
            user="root",
            passwd="123456",
            db="mlc",
            port=3307,
            charset="utf8"
        )
class classification(object):
    def __init (self):
        pass
    
    def get_fina_indicator(self, conn):
        
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        
        sql = "SELECT ts_code, ann_date, eps,total_revenue_ps, undist_profit_ps, gross_margin, fcff, fcfe, tangible_asset, bps, grossprofit_margin,npta FRom financial_data WHERE ann_date >='2023-01-01'and ann_date <'2024-01-01'"
        
        cursor.execute(sql)
        ret = cursor.fetchall()
        
        df = pd.DataFrame(ret)
        
        df1 = df.dropna(subset=['eps','total_revenue_ps', 'undist_profit_ps', 'gross_margin', 'fcff', 'fcfe', 'tangible_asset', 'bps', 'grossprofit_margin','npta'])
        
        df1 = df1.reset_index(drop=False)
        print(df1.head)
        return df1
    
    def get_daily(self, conn, df):
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        new_list = []
        
        for i in range(len(df['ts_code'])):
            ann_date=df['ann_date'][i].strftime('%Y%m%d')
            sql= "select trade_date, closes from date_1 where ts_code= \'"+ df['ts_code'][i]+"\' and trade_date > Date(\' "+ann_date +"\') order by trade_date asc limit 20"

            cursor.execute(sql)
            ret =cursor.fetchall()
            df1=pd.DataFrame(ret)
            print(i)
            
            try:
                if len(df1) > 0:
                    max_close=df1['closes'].max()
                    min_close=df1['closes'].min()
                    the_close=df1['closes'].iloc[1]
                
                    new_list.append({
                        'ts_code':df['ts_code'][i],
                        'ann_date':df['ann_date'][i],
                        'max_close':max_close,
                        'min_close':min_close,
                        'the_close':the_close,
                        'eps':df['eps'][i],
                        'total_revenue_ps':df['total_revenue_ps'][i],
                        'undist_profit_ps':df['undist_profit_ps'][i],
                        'gross_margin':df['gross_margin'][i],
                        'fcff':df['fcff'][i],
                        'fcfe':df['fcfe'][i],
                        'tangible_asset':df['tangible_asset'][i],
                        'bps':df['bps'][i],
                        'grossprofit_margin':df['grossprofit_margin'][i],
                        'npta':df['npta'][i],
                        
                    })
            except Exception as e:
                print(e)
        df2=pd.DataFrame(new_list)
        df2.to_csv('daily.csv',index=False)
            
            
if __name__ == '__main__':
    mu = Mysqlutils()
    ci=classification()
    df = ci.get_fina_indicator(mu.conn)
    ci.get_daily(mu.conn, df)
